#include "sqlconn.h"
#include <QDebug>
#include <QSqlError>
#include <QSqlQuery>

SQLConn::SQLConn(QObject *parent) : QObject(parent)
{
    docSQL<<"id"<<"name"<<"age"<<"sex"<<"work_age"<<"major"<<"attendence"<<"status"<<"phone";
    gueSQL<<"id"<<"name"<<"sex"<<"age"<<"phone"<<"regist_name";
    petSQL<<"id"<<"name"<<"sex"<<"age"<<"kind"<<"owner_id"<<"owner"<<"status";
    this->init();
}

void SQLConn::init()
{
    //判断数据库驱动
    if (QSqlDatabase::drivers().isEmpty())
        qDebug()<<"No database drivers found";

    //绑定数据库
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("pet.db");
    if (!db.open()){
        qDebug() <<"open database fail";
    }

}

QList<Doctor> SQLConn::getDocInfos()
{
    QSqlQuery q(db);
    QList<Doctor> docInfo;
    QString str = "select * from doctor";
    if(!q.exec(str))
    {
         qDebug() <<"getDocInfos:" << q.lastError();
    }
    while(q.next()) {
        int id = q.value(0).toInt();
        QString name = q.value(1).toString();
        int age = q.value(2).toInt();
        QString sex = q.value(3).toString();
        float w_age = q.value(4).toFloat();
        QString major = q.value(5).toString();
        int attendence = q.value(6).toInt();
        int status = q.value(7).toInt();
        QString phone = q.value(8).toString();
        Doctor doc(id,name,age,sex,w_age,major,attendence,status,phone);
        docInfo.push_back(doc);
    }
    return docInfo;
}

QList<Guest> SQLConn::getGueInfos()
{
    QSqlQuery q(db);
    QList<Guest> gueInfo;
    QString str = "select * from custom";
    if(!q.exec(str))
    {
         qDebug() <<"getGueInfos:" << q.lastError();
    }
    while(q.next()) {
        int id = q.value(0).toInt();
        QString name = q.value(1).toString();
        QString sex = q.value(2).toString();
        int age = q.value(3).toInt();
        QString phone = q.value(4).toString();
        QString time = q.value(5).toString();
        Guest gue(id,name,sex,age,phone,time);
        gueInfo.push_back(gue);
    }
    return gueInfo;
}

QList<Pet> SQLConn::getPetInfos()
{
    QSqlQuery q(db);
    QList<Pet> petInfo;
    QString str = "select * from pet";
    if(!q.exec(str))
    {
         qDebug() <<"getPetInfos:" << q.lastError();
    }
    while(q.next()) {
        int id = q.value(0).toInt();
        QString name = q.value(1).toString();
        QString sex = q.value(2).toString();
        float age = q.value(3).toFloat();
        QString kind = q.value(4).toString();
        int owner_id = q.value(5).toInt();
        QString owner = q.value(6).toString();
        QString status = q.value(7).toString();
        Pet pet(id,name,sex,age,kind,owner_id,owner,status, getOwnerPhone(owner_id));
        petInfo.push_back(pet);
    }
    return petInfo;
}

QString SQLConn::getOwnerPhone(int id)
{
    QSqlQuery q(db);
    QString phone;
    QString str = QString("select phone from custom where id=%1").arg(id);
    if(!q.exec(str))
    {
         qDebug() <<"getOwnerPhone:" << q.lastError();
    }
    while(q.next()) {
        phone = q.value(0).toString();
    }
    return phone;
}

QString SQLConn::getOwnerName(int id)
{
    QSqlQuery q(db);
    QString name;
    QString str = QString("select name from custom where id=%1").arg(id);
    if(!q.exec(str))
    {
         qDebug() <<"getOwnerName:" << q.lastError();
    }
    while(q.next()) {
        name = q.value(0).toString();
    }
    return name;
}

void SQLConn::delete_doctor(int id)
{
    QSqlQuery q(db);
    QString str = QString("delete from doctor where id=%1").arg(id);
    if(!q.exec(str))
    {
         qDebug() <<"delete_doctor:" << q.lastError();
    }
//    emit sql_operator_success();
}

void SQLConn::update_doctor(int id, int key, QString value)
{
    QSqlQuery q(db);
    QString phone;
    QString property = docSQL[key];
    QString str;
    if (key==6 || key==7)
    {
        int value1;
        if (value == "是")
        {
            value1 = 1;
        }
        else
        {
            value1 = 0;
        }
        str = QString("update  doctor set %2=%3 where id=%1").arg(id).arg(property).arg(value1);
    }
    if (key==0 || key==2 || key==4)
    {
        str = QString("update  doctor set %2=%3 where id=%1").arg(id).arg(property).arg(value);
    }
    if (key==1 || key==3 || key==5 || key ==8)
    {
        str = QString("update  doctor set %2='%3' where id=%1").arg(id).arg(property).arg(value);
    }
    if(!q.exec(str))
    {
         qDebug() <<"update_doctor:" << q.lastError();
    }
//    emit sql_operator_success();

}

void SQLConn::add_doctor(QString name, int age, QString sex, float work_age, QString major, int attendence, int status, QString phone)
{
    QSqlQuery q(db);
    QString str = QString("insert into doctor (name, age, sex, work_age, major, attendence, status, phone) values ('%1',%2,'%3',%4,'%5',%6,%7,'%8')")
            .arg(name).arg(age).arg(sex).arg(work_age).arg(major).arg(attendence).arg(status).arg(phone);
    if(!q.exec(str))
    {
         qDebug() <<"add_doctor:" << q.lastError();
    }
    emit sql_operator_success();
}

void SQLConn::delete_guest(int id)
{
    QSqlQuery q(db);
    QString phone;
    QString str = QString("delete from custom where id=%1").arg(id);
    if(!q.exec(str))
    {
         qDebug() <<"delete_guest:" << q.lastError();
    }
}

void SQLConn::update_guest(int id, int key, QString value)
{
    QSqlQuery q(db);
    QString property = gueSQL[key];
    QString str;
    if (key==3)
    {
        str = QString("update  custom set %2=%3 where id=%1").arg(id).arg(property).arg(value);
    }
    else
    {
        str = QString("update  custom set %2='%3' where id=%1").arg(id).arg(property).arg(value);
    }
    if(!q.exec(str))
    {
         qDebug() <<"update_guest:" << q.lastError();
    }
}

void SQLConn::add_guest(QString name, QString sex, int age, QString phone, QString time)
{
    QSqlQuery q(db);
    QString str = QString("insert into custom (name, sex, age, phone, regist_time) values ('%1','%2',%3,'%4','%5')")
            .arg(name).arg(sex).arg(age).arg(phone).arg(time);
    if(!q.exec(str))
    {
         qDebug() <<"add_guest:" << q.lastError();
    }
}

void SQLConn::delete_pet(int id)
{
    QSqlQuery q(db);
    QString str = QString("delete from pet where id=%1").arg(id);
    if(!q.exec(str))
    {
         qDebug() <<"delete_pet:" << q.lastError();
    }
}

void SQLConn::update_pet(int id, int key, QString value)
{
    QSqlQuery q(db);
    QString property = petSQL[key];
    QString str;
    if (key==3||key==5)
    {
        str = QString("update  custom set %2=%3 where id=%1").arg(id).arg(property).arg(value);
    }
    else
    {
        str = QString("update  custom set %2='%3' where id=%1").arg(id).arg(property).arg(value);
    }
    if(!q.exec(str))
    {
         qDebug() <<"update_guest:" << q.lastError();
    }
}

void SQLConn::add_pet(QString name, QString sex, float age, QString kind, int ownerid, QString ownername, QString status)
{
    QSqlQuery q(db);
    QString str = QString("insert into pet (name, sex, age, kind, owner_id, owner, status) values ('%1','%2',%3,'%4',%5,'%6','%7')")
            .arg(name).arg(sex).arg(age).arg(kind).arg(ownerid).arg(ownername).arg(status);
    if(!q.exec(str))
    {
         qDebug() <<"add_pet:" << q.lastError();
    }
}

